{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 若无法导入，则pip install xlwings\n",
    "import xlwings as xw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "新建一个表格应用\n",
    "visible：是否可见\n",
    "add_book：是否新建工作簿\n",
    "\"\"\"\n",
    "app = xw.App(visible=True, add_book=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 打开工作表\n",
    "wb = app.books.open('Data_samples/社会实践报名表 - 未填充.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 打开工作簿\n",
    "sht = wb.sheets[\"sheet1\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "<Range [社会实践报名表 - 未填充.xlsx]Sheet1!$A$1>\n目的地\n"
    }
   ],
   "source": [
    "# 读取特定单元格\n",
    "print(sht.range(\"A1\"))\n",
    "print(sht.range(\"A1\").value) #读取值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "目的地\n[['目的地', '描述'], [None, None]]\n[['目的地', '描述'], [None, None]]\n"
    }
   ],
   "source": [
    "print(sht[\"A1\"].value)\n",
    "print(sht[\"A1:B2\"].value)\n",
    "print(sht[\"$A$1:$B$2\"].value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "(1, 5)"
     },
     "metadata": {},
     "execution_count": 21
    }
   ],
   "source": [
    "sht.used_range.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 直接赋值\n",
    "sht[\"A2\"].value = \"阿富汗\" #一一对应"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "['阿富汗', '伊拉克', '约旦', '黎巴嫩', '以色列', '巴勒斯坦', '沙特阿拉伯', '巴林', '卡塔尔', '科威特', '阿拉伯联合酋长国', '阿曼', '也门', '格鲁吉亚', '亚美尼亚', '阿塞拜疆', '土耳其', '塞浦路斯']\n"
    }
   ],
   "source": [
    "countries = [\n",
    "    \"阿富汗\",\n",
    "    \"伊拉克\",\n",
    "    \"约旦\",\n",
    "    \"黎巴嫩\",\n",
    "    \"以色列\",\n",
    "    \"巴勒斯坦\",\n",
    "    \"沙特阿拉伯\",\n",
    "    \"巴林\",\n",
    "    \"卡塔尔\",\n",
    "    \"科威特\",\n",
    "    \"阿拉伯联合酋长国\",\n",
    "    \"阿曼\",\n",
    "    \"也门\",\n",
    "    \"格鲁吉亚\",\n",
    "    \"亚美尼亚\",\n",
    "    \"阿塞拜疆\",\n",
    "    \"土耳其\",\n",
    "    \"塞浦路斯\",\n",
    "]\n",
    "print(countries)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 默认按照行赋值\n",
    "sht[\"A2\"].value = countries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 范围赋值\n",
    "sht[\"B2:Z2\"].value = \"\" # 把范围 B2:Z2 赋值为空字符串，即清空"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 按列赋值\n",
    "sht[\"A2\"].options(transpose=True).value = countries #加一句转置"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 用随机数填充第二列\n",
    "import random\n",
    "description = [\"第\"+str(random.randint(2,8))+\"公司\" for x in range(20)]\n",
    "sht[\"B2\"].options(transpose=True).value = description[:len(countries)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 随机选几个院系填充第三列\n",
    "depart_list = [\"电子\",\"自动化\",\"工物\",\"土木\",\"水利\",\"社科\",\"航院\"]\n",
    "\n",
    "depart = [depart_list[random.randint(0,len(depart_list))-1]for x in range(20)]\n",
    "sht[\"C2\"].options(transpose=True).value = depart[:len(countries)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 随机选几个院系填充第四列\n",
    "depart = [random.randint(1,20) for x in range(20)]\n",
    "sht[\"D2\"].options(transpose=True).value = depart[:len(countries)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 第五列\n",
    "plan = [random.choice([\"唱\",\"跳\",\"rap\",\"篮球\"]) for x in range(20)]\n",
    "sht[\"e2\"].options(transpose=True).value = plan[:len(countries)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 关闭workbook 和 app"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用结束一定要记得关闭 workbook、退出 app\n",
    "wb.save() #保存工作表\n",
    "wb.close() #关闭工作簿\n",
    "app.quit() #退出表格应用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}